import openpyxl

# 打开Excel文件1
excel_source_name = '交车一周年-不包含TAYCAN_20231215134810.xlsx'
workbook_source = openpyxl.load_workbook(excel_source_name)

# 获取工作表
sheet_no_taycan = workbook_source.active

# 打开Excel文件2
excel_name = '交车一周年-仅TAYCAN车主-短信_20231215134812.xlsx'
workbook = openpyxl.load_workbook(excel_name)

# 获取工作表
sheet_taycan = workbook.active

# 获取模版文件(交车一周年-不包含TAYCAN-常规1.xlsx)
no_taycan_basis_excel_name = '交车一周年-不包含TAYCAN-常规1.xlsx'
no_taycan_basis_workbook = openpyxl.load_workbook(no_taycan_basis_excel_name)
no_taycan_basis_sheet = no_taycan_basis_workbook.active

# 获取模版文件(交车一周年-不包含TAYCAN-不含称谓.xlsx)
no_taycan_excel_name = '交车一周年-不包含TAYCAN-不含称谓.xlsx'
no_taycan_workbook = openpyxl.load_workbook(no_taycan_excel_name)
no_taycan_sheet = no_taycan_workbook.active

# 获取模版文件(交车一周年-仅TAYCAN车主-短信-常规.xlsx)
taycan_basis_excel_name = '交车一周年-仅TAYCAN车主-短信-常规.xlsx'
taycan_basis_workbook = openpyxl.load_workbook(taycan_basis_excel_name)
taycan_basis_sheet = taycan_basis_workbook.active

# 获取模版文件(交车一周年-仅TAYCAN车主-短信-不含称谓.xlsx)
taycan_excel_name = '交车一周年-仅TAYCAN车主-短信-不含称谓.xlsx'
taycan_workbook = openpyxl.load_workbook(no_taycan_excel_name)
taycan_sheet = no_taycan_workbook.active


# 遍历指定列的所有单元格，获取用户名数据
for row in sheet_no_taycan.iter_rows(min_row=2):   # 从第二行开始，因为第一行可能是标题行
    # 获取除了关注之外的数据 并且手机号不能为空
    if row[2].value != '关注' and row[1].value != '':
        # 过滤手机号不包含有中文逗号的数据
        if '，' not in row[1].value:
            # 过滤姓名包含中文逗号或保时捷的数据
            if '，' in row[3].value or '保时捷' in row[3].value:
                # 输出该行的整行数据
                openid = [cell.value for cell in row][1]
                no_taycan_sheet.append([openid, '保时捷车主'])
                # print(openid)
            # 过滤掉称谓不含女士和先生的数据
            elif row[4].value != '女士' and row[4].value != '先生':
                openid = [cell.value for cell in row][1]
                no_taycan_sheet.append([openid, '保时捷车主'])
                # print(openid)
            # 常规数据
            else:
                openid_1 = [cell.value for cell in row][1]
                openid_3 = [cell.value for cell in row][3]
                openid_4 = [cell.value for cell in row][4]
                no_taycan_basis_sheet.append([openid_1, openid_3, openid_4])
                # print(openid_1, openid_3, openid_4)

for row in sheet_no_taycan.iter_rows(min_row=2):   # 从第二行开始，因为第一行可能是标题行
    # 获取除了关注之外的数据 并且手机号不能为空
    if row[2].value != '关注' and row[1].value != '':
        # 过滤手机号不包含有中文逗号的数据
        if '，' not in row[1].value:
            # 过滤姓名包含中文逗号或保时捷的数据
            if '，' in row[3].value or '保时捷' in row[3].value:
                # 输出该行的整行数据
                openid = [cell.value for cell in row][1]
                no_taycan_sheet.append([openid, '保时捷车主'])
                # print(openid)
            # 过滤掉称谓不含女士和先生的数据
            elif row[4].value != '女士' and row[4].value != '先生':
                openid = [cell.value for cell in row][1]
                no_taycan_sheet.append([openid, '保时捷车主'])
                # print(openid)
            # 常规数据
            else:
                openid_1 = [cell.value for cell in row][1]
                openid_3 = [cell.value for cell in row][3]
                openid_4 = [cell.value for cell in row][4]
                no_taycan_basis_sheet.append([openid_1, openid_3, openid_4])
                # print(openid_1, openid_3, openid_4)

# 保存推送的Excel文件
no_taycan_workbook.save(no_taycan_excel_name)
no_taycan_basis_workbook.save(no_taycan_basis_excel_name)
taycan_workbook.save(taycan_excel_name)
taycan_basis_workbook.save(taycan_basis_excel_name)